package database

import (
	"context"
	"database/sql"
	"fmt"
	"time"

	"github.com/rs/zerolog"
	"github.com/rs/zerolog/log"
	"gorm.io/driver/postgres"
	"gorm.io/gorm"
	"gorm.io/gorm/logger"
	"techoiceness.com/aiagent/llm-gateway/internal/config"
	"techoiceness.com/aiagent/llm-gateway/internal/entity"
)

// DatabaseManager 数据库管理器
type DatabaseManager struct {
	db  *gorm.DB
	cfg *config.DatabaseConfig
}

type DBLogger struct {
	zLogger            zerolog.Logger
	slowQueryThreshold time.Duration
}

var _ logger.Interface = (*DBLogger)(nil)

func NewDBLogger(cfg *config.DatabaseConfig) *DBLogger {
	zLogger := log.With().Str("module", "database").Logger()

	// 设置慢查询阈值，默认1秒
	threshold := 1 * time.Second
	if cfg != nil && cfg.SlowQueryThreshold > 0 {
		threshold = time.Duration(cfg.SlowQueryThreshold) * time.Millisecond
	}

	return &DBLogger{
		zLogger:            zLogger,
		slowQueryThreshold: threshold,
	}
}
func (l *DBLogger) LogMode(level logger.LogLevel) logger.Interface {
	switch level {
	case logger.Silent:
		l.zLogger = l.zLogger.Level(zerolog.Disabled)
	case logger.Error:
		l.zLogger = l.zLogger.Level(zerolog.ErrorLevel)
	case logger.Warn:
		l.zLogger = l.zLogger.Level(zerolog.WarnLevel)
	case logger.Info:
		l.zLogger = l.zLogger.Level(zerolog.InfoLevel)
	default:
		l.zLogger = l.zLogger.Level(zerolog.DebugLevel)
	}
	return l
}

func (l *DBLogger) Info(ctx context.Context, msg string, data ...interface{}) {
	l.zLogger.Info().Msgf(msg, data...)
}

func (l *DBLogger) Warn(ctx context.Context, msg string, data ...interface{}) {
	l.zLogger.Warn().Msgf(msg, data...)
}

func (l *DBLogger) Error(ctx context.Context, msg string, data ...interface{}) {
	l.zLogger.Error().Msgf(msg, data...)
}

func (l *DBLogger) Trace(ctx context.Context, begin time.Time, fc func() (string, int64), err error) {
	elapsed := time.Since(begin)
	sql, rows := fc()

	if err != nil {
		// 记录错误的查询
		l.zLogger.Error().Err(err).Str("sql", sql).Int64("rows", rows).Dur("elapsed", elapsed).Msg("SQL执行错误")
	} else if elapsed > l.slowQueryThreshold {
		// 记录慢查询
		l.zLogger.Warn().Str("sql", sql).Int64("rows", rows).Dur("elapsed", elapsed).Msg("慢查询检测")
	}
	// 其他正常查询不记录日志
}

// NewDatabase 创建数据库连接
func NewDatabase(cfg *config.DatabaseConfig) (*gorm.DB, error) {
	manager := &DatabaseManager{
		cfg: cfg,
	}

	return manager.connect()
}

// connect 建立数据库连接
func (dm *DatabaseManager) connect() (*gorm.DB, error) {
	// 构建 DSN
	dsn := fmt.Sprintf(
		"host=%s port=%d user=%s password=%s dbname=%s sslmode=%s TimeZone=%s",
		dm.cfg.Host,
		dm.cfg.Port,
		dm.cfg.Username,
		dm.cfg.Password,
		dm.cfg.Name,
		getSSLMode(dm.cfg),
		getTimeZone(dm.cfg),
	)

	// 配置 GORM
	gormConfig := &gorm.Config{
		DisableForeignKeyConstraintWhenMigrating: true,
		Logger:                                   NewDBLogger(dm.cfg),
		NowFunc: func() time.Time {
			return time.Now().Local()
		},
	}

	db, err := gorm.Open(postgres.Open(dsn), gormConfig)
	if err != nil {
		return nil, fmt.Errorf("failed to connect database: %w", err)
	}

	// 配置连接池
	if err := dm.configureConnectionPool(db); err != nil {
		return nil, fmt.Errorf("failed to configure connection pool: %w", err)
	}

	dm.db = db
	return db, nil
}

// configureConnectionPool 配置连接池
func (dm *DatabaseManager) configureConnectionPool(db *gorm.DB) error {
	sqlDB, err := db.DB()
	if err != nil {
		return fmt.Errorf("failed to get sql.DB: %w", err)
	}

	// 设置连接池参数
	maxOpenConns := getMaxOpenConns(dm.cfg)
	maxIdleConns := getMaxIdleConns(dm.cfg)
	maxLifetime := getMaxLifetime(dm.cfg)
	maxIdleTime := getMaxIdleTime(dm.cfg)

	sqlDB.SetMaxOpenConns(maxOpenConns)
	sqlDB.SetMaxIdleConns(maxIdleConns)
	sqlDB.SetConnMaxLifetime(maxLifetime)
	sqlDB.SetConnMaxIdleTime(maxIdleTime)

	log.Info().
		Str("process", "configureConnectionPool").
		Msg("数据库连接池配置完成")

	return nil
}

// HealthCheck 健康检查
func HealthCheck(db *gorm.DB) error {
	ctx, cancel := context.WithTimeout(context.Background(), 5*time.Second)
	defer cancel()

	sqlDB, err := db.DB()
	if err != nil {
		return fmt.Errorf("failed to get sql.DB: %w", err)
	}

	if err := sqlDB.PingContext(ctx); err != nil {
		return fmt.Errorf("database ping failed: %w", err)
	}

	return nil
}

// GetConnectionStats 获取连接统计信息
func GetConnectionStats(db *gorm.DB) (*sql.DBStats, error) {
	sqlDB, err := db.DB()
	if err != nil {
		return nil, fmt.Errorf("failed to get sql.DB: %w", err)
	}

	stats := sqlDB.Stats()
	return &stats, nil
}

// 辅助函数
func getSSLMode(cfg *config.DatabaseConfig) string {
	if cfg.SSLMode != "" {
		return cfg.SSLMode
	}
	return "disable"
}

func getTimeZone(cfg *config.DatabaseConfig) string {
	if cfg.TimeZone != "" {
		return cfg.TimeZone
	}
	return "UTC"
}

func getMaxOpenConns(cfg *config.DatabaseConfig) int {
	if cfg.MaxOpenConns > 0 {
		return cfg.MaxOpenConns
	}
	return 25
}

func getMaxIdleConns(cfg *config.DatabaseConfig) int {
	if cfg.MaxIdleConns > 0 {
		return cfg.MaxIdleConns
	}
	return 10
}

func getMaxLifetime(cfg *config.DatabaseConfig) time.Duration {
	if cfg.MaxLifetime > 0 {
		return time.Duration(cfg.MaxLifetime) * time.Second
	}
	return 5 * time.Minute
}

func getMaxIdleTime(cfg *config.DatabaseConfig) time.Duration {
	if cfg.MaxIdleTime > 0 {
		return time.Duration(cfg.MaxIdleTime) * time.Second
	}
	return 10 * time.Minute
}

// Migrate 兼容性迁移函数（保留以防其他地方使用）
func Migrate(db *gorm.DB) error {
	return MigrateSystem(db)
}

// MigrateSystem 迁移系统数据库
func MigrateSystem(db *gorm.DB) error {
	return db.AutoMigrate(
		&entity.User{},
		&entity.Conversation{},
		&entity.LLMMessage{},
		&entity.ClientMessage{},
		&entity.SystemPrompt{},     // 系统提示词配置表
		&entity.Admin{},            // 管理员表
		&entity.Tool{},             // 工具表
		&entity.ToolDBConnection{}, // 工具数据库连接配置表（存储在系统数据库）
		&entity.LLMProvider{},      // LLM提供商配置表
	)
}

type TableColumn struct {
	ColumnName    string  `json:"columnName"`
	DataType      string  `json:"dataType"`
	IsNullable    bool    `json:"isNullable"`
	ColumnDefault *string `json:"columnDefault"`
	IsPrimaryKey  bool    `json:"isPrimaryKey"`
}

type TableStructure struct {
	TableName string        `json:"tableName"`
	Schema    string        `json:"schema"`
	Columns   []TableColumn `json:"columns"`
}

func GetAllTableStructures(db *gorm.DB) ([]TableStructure, error) {
	// 表信息结构体，用于查询数据库表结构
	type TableInfo struct {
		TableName string `json:"tableName"`
		Schema    string `json:"schema"`
	}

	var tableInfos []TableInfo
	err := db.Raw(`
		SELECT table_name, table_schema as schema
		FROM information_schema.tables
		WHERE table_type = 'BASE TABLE' AND table_schema NOT IN ('pg_catalog', 'information_schema')
	`).Scan(&tableInfos).Error
	if err != nil {
		return nil, err
	}

	var structures []TableStructure
	for _, tableInfo := range tableInfos {
		var columns []TableColumn
		err = db.Raw(`
			SELECT column_name, data_type, 
			       CASE WHEN is_nullable = 'YES' THEN true ELSE false END AS is_nullable,
			       column_default,
			       CASE WHEN column_name = ANY(ARRAY(SELECT a.attname FROM pg_index i JOIN pg_attribute a ON a.attrelid = i.indrelid AND a.attnum = ANY(i.indkey) WHERE i.indisprimary)) THEN true ELSE false END AS is_primary_key
			FROM information_schema.columns
			WHERE table_name = ? AND table_schema = ?
		`, tableInfo.TableName, tableInfo.Schema).Scan(&columns).Error
		if err != nil {
			return nil, err
		}

		structure := TableStructure{
			TableName: tableInfo.TableName,
			Schema:    tableInfo.Schema,
			Columns:   columns,
		}
		structures = append(structures, structure)
	}

	return structures, nil
}
